<?php

class model_friend
{
	private $db = false;

	public function __construct() {
		require(BASEPATH.'config/database.php');
		$this->db = mysql_connect($db[ACTIVE_COMPETITION]['hostname'], $db[ACTIVE_COMPETITION]['username'], $db[ACTIVE_COMPETITION]['password']);
		if (! $this->db)
		{
			log_message('error', 'could not connect to database '.mysql_error());
		}
		mysql_select_db($db[ACTIVE_COMPETITION]['database'], $this->db);
		db_set_charset($db[ACTIVE_COMPETITION]['char_set'], $db[ACTIVE_COMPETITION]['dbcollat'], $this->db);

	}
	// return false if failed; return user_login if success
	public function get_user_by_name($username_) {
		if ($username_ == null) {
			return null;
		}
		$sql = "SELECT userid, username FROM user WHERE username = '$username_'" ;
		log_message ( 'debug', "get_user_by_name $sql" );
		$query = mysql_query ( $sql, $this->db );
		if (mysql_num_rows($query) > 0) {
			if (mysql_num_rows($query) > 1) {
				log_message ( 'error', "database operation get_user_by_name '$username_' has ".mysql_num_rows($query) );
			}
			$row =  mysql_fetch_object($query);
			return $row;
		} else {
			return null;
		}
	}
	public function search_user_by_username( $username_ ) {
			$sql = "SELECT user.userid, user.username, user.plist, user.update_time
				FROM user
				WHERE user.username LIKE '%.".$this->db->escape_like_str($username_)."%' ";
		log_message('debug', 'search_user_by_username '.$sql);
		$query = $this->db->query( $sql );
		return $query;
	}
	public function update_user_dress( $userid_, $username_, $dress_plist_, $md5sum_, $home_index_, $level_, $age_, $star_, $weight_, $height_, $time_ ) {
		$sql = "SELECT userid FROM user WHERE userid = $userid_ ";
		$query = mysql_query ( $sql, $this->db );

		if (mysql_num_rows($query) > 0) {
			log_message('debug', $sql);
			$sql = "UPDATE user
			SET plist = '$dress_plist_', md5sum = '$md5sum_', home = $home_index_, level = $level_, age = $age_, star = $star_, weight = $weight_, height = $height_, update_time = '$time_'
			WHERE userid = $userid_ ";
			log_message('debug', 'update_user_dress ');
			mysql_query ( $sql, $this->db );
			if (mysql_affected_rows($this->db) > 0) {
				return true;
			}
			else {
				log_message('debug', 'update_user_dress failed '.$sql);
				return false;
			}
		}
		else {
			$sql = "INSERT INTO user(userid, username, plist, md5sum, home, level, age, star, weight, height, update_time)
			VALUES( $userid_, ".$this->db->escape($username_)." , '$dress_plist_', '$md5sum_', $home_index_, $level_, $age_, $star_, $weight_, $height_, '$time_')";
			//log_message('debug', 'add_user_dress '.$sql);
			mysql_query ( $sql, $this->db );
			if (mysql_affected_rows($this->db) > 0) {
				return true;
			}
			else {
				log_message('error', 'add_user_dress failed '.$sql);
				return false;
			}
		}
	}
	public function add_attention( $inviter_, $invitee_, $time_ ) {
		$sql = "INSERT INTO friend( inviter, invitee, invite_time) SELECT $inviter_, $invitee_, '$time_' FROM dual WHERE NOT EXISTS (SELECT * FROM friend WHERE inviter = $inviter_ AND invitee = $invitee_)";
		log_message('debug', 'add_attention '.$sql);
		mysql_query ( $sql, $this->db );
		if (mysql_affected_rows($this->db) > 0) {
			return true;
		}
		else {
			log_message('error', "add_attention has exist inviter $inviter_, invitee  $invitee_  ".mysql_error());
			return false;
		}
	}
	public function remove_attention( $inviter_, $invitee_ ) {
		$sql = "DELETE FROM friend WHERE inviter = $inviter_ AND invitee = $invitee_";
		log_message('debug', 'remove_attention '.$sql);
		$query = mysql_query ( $sql, $this->db );
		if (mysql_affected_rows($this->db) > 0) {
			return true;
		}
		else {
			log_message('error', 'remove_attention failed '.$sql);
			return false;
		}
	}
	public function get_friend_state( $inviter_, $invitee_ ) {
		$sql = "SELECT state FROM friend WHERE inviter = $inviter_ AND invitee = $invitee_";
		$query = mysql_query ( $sql, $this->db );
		if (mysql_num_rows($query) > 0) {
			return $query->row(0)->state;
		}
		else {
			log_message('error', 'get_friend_state have no friend relation '.$sql);
			return null;
		}
	}
	public function set_friend_state( $inviter_, $invitee_, $flag_ ) {
		$sql = "UPDATE friend SET state = $flag_ WHERE inviter = $inviter_ AND invitee = $invitee_";
		log_message('debug', 'set_friend_state '.$sql);
		$query = mysql_query ( $sql, $this->db );
		if (mysql_affected_rows($this->db) > 0) {
			return true;
		}
		else {
			log_message('error', 'set_friend_state failed '.$sql);
			return false;
		}
	}
	public function update_introduction( $userid_, $msg_, $time_ ) {
		$sql = "UPDATE user SET user.introduction = ".$this->db->escape($msg_).", update_time = '$time_' WHERE user.userid = $userid_ ";
		log_message('debug', 'update_introduction '.$sql);
		$query = mysql_query ( $sql, $this->db );
		if (mysql_affected_rows($this->db) > 0) {
			return true;
		}
		else {
			log_message('error', 'update_introduction failed '.$sql);
			return false;
		}
	}
	public function add_message( $inviter_, $invitee_, $type_, $time_ ) {
		$sql = "INSERT INTO message (inviter, invitee, msg_type, send_time) SELECT $inviter_, $invitee_, $type_, '$time_' FROM dual WHERE NOT EXISTS (SELECT messageid FROM message WHERE inviter = $inviter_ AND invitee = $invitee_ AND msg_type = $type_)";
		log_message('debug', 'add_message '.$sql);
		$query = mysql_query ( $sql, $this->db );
		if (mysql_affected_rows($this->db) > 0) {
			return true;
		}
		else {
			log_message('error', "add_message have exist inviter $inviter_ invitee $invitee_ msg_type $type_");
			return false;
		}
	}
	public function add_message_to_all( $inviter_, $type_, $time_ ) {
		$sql = "INSERT INTO message (inviter, invitee, msg_type, send_time) SELECT $inviter_, friend.inviter, $type_, '$time_' FROM friend WHERE friend.invitee = $inviter_ AND NOT EXISTS (SELECT message.messageid FROM message WHERE message.inviter = $inviter_ AND message.invitee = friend.inviter AND message.msg_type = $type_)";
		log_message('debug', 'add_message_to_all '.$sql);
		$query = mysql_query ( $sql, $this->db );
		if (mysql_affected_rows($this->db) > 0) {
			return true;
		}
		else {
			log_message('error', 'add_message_to_all affect 0 line'.$sql);
			return false;
		}
	}
	public function remove_message( $messageid_ ) {
		$sql = "DELETE FROM message WHERE messageid = $messageid_ ";
		log_message('debug', 'remove_message '.$sql);
		$query = mysql_query ( $sql, $this->db );
		if (mysql_affected_rows($this->db) > 0) {
			return true;
		}
		else {
			log_message('error', 'remove_message '.$sql);
			return false;
		}
	}
	public function get_message_list( $invitee_, $time_ ) {
		$sql = "SELECT user.userid as senderid, a.invitee as receiverid, user.username as sender_name, a.messageid, a.msg_type, a.send_time
		FROM user INNER JOIN ( SELECT messageid, inviter, invitee, msg_type, send_time
		FROM message
		WHERE message.invitee = $invitee_ AND send_time >= '$time_'
		AND NOT EXISTS ( SELECT friend.inviter
		FROM friend
		WHERE friend.inviter = message.inviter AND friend.invitee = message.invitee AND friend.state = 1)
		) as a
		ON user.userid = a.inviter
		ORDER BY send_time ";

		log_message('debug', 'get_message_list '.$sql);
		$query = mysql_query ( $sql, $this->db );
		if (mysql_num_rows($query) > 0) {
			return $query;
		}
		else {
			return null;
		}
	}
	public function get_attention_list( $inviter_, $time_ ) {
		$sql = "SELECT user.userid, user.username, user.introduction, user.plist, user.md5sum,
		user.home, user.update_time, user.level, user.age, user.star, user.weight, user.height, a.state,
		user.cover_gril_count, user.cover_gril_best_score, user.cover_gril_best_ranking, user.cover_gril_gold, user.cover_gril_silver, user.cover_gril_bronze,
		user.pk_count, user.pk_best_score, user.pk_best_ranking, user.pk_gold, user.pk_silver, user.pk_bronze
		FROM user INNER JOIN ( SELECT friend.inviter, friend.invitee, friend.state, friend.invite_time FROM friend
		WHERE friend.inviter = $inviter_ ) as a ON user.userid = a.invitee
		WHERE user.update_time > '$time_' OR invite_time > '$time_' ";

		log_message('debug', 'get_attention_list '.$sql);
		$query = mysql_query ( $sql, $this->db );
		if (mysql_num_rows($query) > 0) {
			return $query;
		}
		else {
			return null;
		}
	}
	public function get_attention( $inviter_, $invitee_ ) {
		$sql = "SELECT user.userid, user.username, user.introduction, user.plist, user.md5sum,
		user.home, user.update_time, user.level, user.age, user.star, user.weight, user.height, a.state,
		user.cover_gril_count, user.cover_gril_best_score, user.cover_gril_best_ranking, user.cover_gril_gold, user.cover_gril_silver, user.cover_gril_bronze,
		user.pk_count, user.pk_best_score, user.pk_best_ranking, user.pk_gold, user.pk_silver, user.pk_bronze
		FROM user INNER JOIN ( SELECT friend.inviter, friend.invitee, friend.state FROM friend
		WHERE friend.inviter = $inviter_ AND friend.invitee = $invitee_ ) as a ON user.userid = a.invitee";
		log_message('debug', 'get_attention '.$sql);
		$query = mysql_query ( $sql, $this->db );
		if (mysql_num_rows($query) > 0) {
			return $query;
		}
		else {
			return null;
		}
	}
	public function get_fans_list( $invitee_, $time_ ) {

		$sql = "SELECT user.userid, user.username, user.introduction, user.plist, user.md5sum,
		user.home, user.update_time, user.level, user.age, user.star, user.weight, user.height, a.state,
		user.cover_gril_count, user.cover_gril_best_score, user.cover_gril_best_ranking, user.cover_gril_gold, user.cover_gril_silver, user.cover_gril_bronze,
		user.pk_count, user.pk_best_score, user.pk_best_ranking, user.pk_gold, user.pk_silver, user.pk_bronze
		FROM user INNER JOIN ( SELECT friend.inviter, friend.invitee, friend.state, friend.invite_time FROM friend
		WHERE friend.invitee = $invitee_ ) as a ON user.userid = a.inviter
		WHERE user.update_time > '$time_' OR invite_time > '$time_' " ;

		log_message('debug', 'get_fans_list '.$sql);
		$query = mysql_query ( $sql, $this->db );
		if (mysql_num_rows($query) > 0) {
			return $query;
		}
		else {
			return null;
		}
	}

	public function cover_gril_vote($voted_userid, $time) {
		$sql = "call proc_vote($voted_userid, '$time', @num_ticket, @ranking)";
		log_message ( 'debug', "database operation cover_gril_vote 1 $sql  " );
		$query = mysql_query ( $sql, $this->db );
		$sql = "SELECT @num_ticket as num_ticket, @ranking as ranking";
		log_message ( 'debug', "database operation cover_gril_vote  2 $sql  " );
		$query1 = mysql_query ( $sql, $this->db );
		if (mysql_num_rows($query1) > 0) {
			log_message ( 'debug', "database operation cover_gril_vote 3  $sql  ".mysql_fetch_object($query1)->num_ticket."   ".mysql_fetch_object($query1)->ranking );
			return true;
		} else {
			log_message ( 'error', "database operation cover_gril_vote error $sql" );
			return null;
		}
	}
}
